use sea_orm_migration::prelude::*;

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        // Create users table
        manager
            .create_table(
                Table::create()
                    .table(Users::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(Users::Id)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(
                        ColumnDef::new(Users::Email)
                            .string()
                            .not_null()
                            .unique_key(),
                    )
                    .col(ColumnDef::new(Users::HashedPassword).string().not_null())
                    .col(ColumnDef::new(Users::AvatarUrl).string())
                    .col(
                        ColumnDef::new(Users::IsAdmin)
                            .boolean()
                            .not_null()
                            .default(false),
                    )
                    .col(ColumnDef::new(Users::CreatedAt).timestamp().not_null())
                    .col(ColumnDef::new(Users::UpdatedAt).timestamp().not_null())
                    .to_owned(),
            )
            .await?;

        // Create data_items table
        manager
            .create_table(
                Table::create()
                    .table(DataItems::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(DataItems::Id)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(ColumnDef::new(DataItems::Name).string().not_null())
                    .col(ColumnDef::new(DataItems::Description).string())
                    .col(ColumnDef::new(DataItems::Project).string())
                    .col(ColumnDef::new(DataItems::Source).string().not_null())
                    .col(ColumnDef::new(DataItems::FilePath).string().not_null())
                    .col(ColumnDef::new(DataItems::FileHash).string())
                    .col(ColumnDef::new(DataItems::FileSize).integer())
                    .col(ColumnDef::new(DataItems::FileType).string())
                    .col(
                        ColumnDef::new(DataItems::IsFolder)
                            .boolean()
                            .not_null()
                            .default(false),
                    )
                    .col(ColumnDef::new(DataItems::FileCount).integer())
                    .col(ColumnDef::new(DataItems::UserId).integer().not_null())
                    .col(ColumnDef::new(DataItems::ParentId).integer())
                    .col(
                        ColumnDef::new(DataItems::IsPublic)
                            .boolean()
                            .not_null()
                            .default(false),
                    )
                    .col(ColumnDef::new(DataItems::CreatedAt).timestamp().not_null())
                    .col(ColumnDef::new(DataItems::UpdatedAt).timestamp().not_null())
                    .col(ColumnDef::new(DataItems::DvcMetadataId).integer())
                    .foreign_key(
                        ForeignKey::create()
                            .name("fk-data_items-user_id")
                            .from(DataItems::Table, DataItems::UserId)
                            .to(Users::Table, Users::Id)
                            .on_delete(ForeignKeyAction::Cascade),
                    )
                    .foreign_key(
                        ForeignKey::create()
                            .name("fk-data_items-parent_id")
                            .from(DataItems::Table, DataItems::ParentId)
                            .to(DataItems::Table, DataItems::Id)
                            .on_delete(ForeignKeyAction::Cascade),
                    )
                    .to_owned(),
            )
            .await?;

        // Create dvc_metadata table
        manager
            .create_table(
                Table::create()
                    .table(DvcMetadata::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(DvcMetadata::Id)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(ColumnDef::new(DvcMetadata::UserId).integer().not_null())
                    .col(ColumnDef::new(DvcMetadata::DvcFilename).string().not_null())
                    .col(
                        ColumnDef::new(DvcMetadata::DataFilename)
                            .string()
                            .not_null(),
                    )
                    .col(
                        ColumnDef::new(DvcMetadata::DataFileHash)
                            .string()
                            .not_null(),
                    )
                    .col(ColumnDef::new(DvcMetadata::DataFileSize).integer())
                    .col(ColumnDef::new(DvcMetadata::DvcFilePath).string())
                    .col(ColumnDef::new(DvcMetadata::DvcFileHash).string())
                    .col(ColumnDef::new(DvcMetadata::Project).string())
                    .col(ColumnDef::new(DvcMetadata::Source).string().not_null())
                    .col(
                        ColumnDef::new(DvcMetadata::CreatedAt)
                            .timestamp()
                            .not_null(),
                    )
                    .col(
                        ColumnDef::new(DvcMetadata::UpdatedAt)
                            .timestamp()
                            .not_null(),
                    )
                    .foreign_key(
                        ForeignKey::create()
                            .name("fk-dvc_metadata-user_id")
                            .from(DvcMetadata::Table, DvcMetadata::UserId)
                            .to(Users::Table, Users::Id)
                            .on_delete(ForeignKeyAction::Cascade),
                    )
                    .to_owned(),
            )
            .await?;

        // Create data_lineage table
        manager
            .create_table(
                Table::create()
                    .table(DataLineage::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(DataLineage::Id)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(ColumnDef::new(DataLineage::ChildId).integer().not_null())
                    .col(ColumnDef::new(DataLineage::ParentId).integer().not_null())
                    .col(
                        ColumnDef::new(DataLineage::RelationshipType)
                            .string()
                            .not_null()
                            .default("derived_from"),
                    )
                    .col(
                        ColumnDef::new(DataLineage::Confidence)
                            .double()
                            .not_null()
                            .default(1.0),
                    )
                    .col(
                        ColumnDef::new(DataLineage::DetectionMethod)
                            .string()
                            .not_null()
                            .default("manual"),
                    )
                    .col(ColumnDef::new(DataLineage::Description).string())
                    .col(ColumnDef::new(DataLineage::CreatedBy).integer().not_null())
                    .col(
                        ColumnDef::new(DataLineage::CreatedAt)
                            .timestamp()
                            .not_null(),
                    )
                    .col(
                        ColumnDef::new(DataLineage::UpdatedAt)
                            .timestamp()
                            .not_null(),
                    )
                    .foreign_key(
                        ForeignKey::create()
                            .name("fk-data_lineage-child_id")
                            .from(DataLineage::Table, DataLineage::ChildId)
                            .to(DataItems::Table, DataItems::Id)
                            .on_delete(ForeignKeyAction::Cascade),
                    )
                    .foreign_key(
                        ForeignKey::create()
                            .name("fk-data_lineage-parent_id")
                            .from(DataLineage::Table, DataLineage::ParentId)
                            .to(DataItems::Table, DataItems::Id)
                            .on_delete(ForeignKeyAction::Cascade),
                    )
                    .foreign_key(
                        ForeignKey::create()
                            .name("fk-data_lineage-created_by")
                            .from(DataLineage::Table, DataLineage::CreatedBy)
                            .to(Users::Table, Users::Id)
                            .on_delete(ForeignKeyAction::Cascade),
                    )
                    .to_owned(),
            )
            .await?;

        // Create lineage_suggestions table
        manager
            .create_table(
                Table::create()
                    .table(LineageSuggestions::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(LineageSuggestions::Id)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(
                        ColumnDef::new(LineageSuggestions::ChildId)
                            .integer()
                            .not_null(),
                    )
                    .col(
                        ColumnDef::new(LineageSuggestions::SuggestedParentId)
                            .integer()
                            .not_null(),
                    )
                    .col(
                        ColumnDef::new(LineageSuggestions::ConfidenceScore)
                            .double()
                            .not_null(),
                    )
                    .col(
                        ColumnDef::new(LineageSuggestions::SuggestionReason)
                            .string()
                            .not_null(),
                    )
                    .col(
                        ColumnDef::new(LineageSuggestions::DetectionMethod)
                            .string()
                            .not_null(),
                    )
                    .col(
                        ColumnDef::new(LineageSuggestions::Status)
                            .string()
                            .not_null()
                            .default("pending"),
                    )
                    .col(
                        ColumnDef::new(LineageSuggestions::CreatedAt)
                            .timestamp()
                            .not_null(),
                    )
                    .col(ColumnDef::new(LineageSuggestions::ReviewedAt).timestamp())
                    .col(ColumnDef::new(LineageSuggestions::ReviewedBy).integer())
                    .foreign_key(
                        ForeignKey::create()
                            .name("fk-lineage_suggestions-child_id")
                            .from(LineageSuggestions::Table, LineageSuggestions::ChildId)
                            .to(DataItems::Table, DataItems::Id)
                            .on_delete(ForeignKeyAction::Cascade),
                    )
                    .foreign_key(
                        ForeignKey::create()
                            .name("fk-lineage_suggestions-suggested_parent_id")
                            .from(
                                LineageSuggestions::Table,
                                LineageSuggestions::SuggestedParentId,
                            )
                            .to(DataItems::Table, DataItems::Id)
                            .on_delete(ForeignKeyAction::Cascade),
                    )
                    .to_owned(),
            )
            .await?;

        // Create indexes
        manager
            .create_index(
                Index::create()
                    .name("idx-data_items-user_id")
                    .table(DataItems::Table)
                    .col(DataItems::UserId)
                    .to_owned(),
            )
            .await?;

        manager
            .create_index(
                Index::create()
                    .name("idx-data_items-file_hash")
                    .table(DataItems::Table)
                    .col(DataItems::FileHash)
                    .to_owned(),
            )
            .await?;

        manager
            .create_index(
                Index::create()
                    .name("idx-data_lineage-child_id")
                    .table(DataLineage::Table)
                    .col(DataLineage::ChildId)
                    .to_owned(),
            )
            .await?;

        manager
            .create_index(
                Index::create()
                    .name("idx-data_lineage-parent_id")
                    .table(DataLineage::Table)
                    .col(DataLineage::ParentId)
                    .to_owned(),
            )
            .await?;

        // Seed initial users
        let insert_result = manager.get_connection().execute_unprepared(
            r#"
            INSERT OR IGNORE INTO users (email, hashed_password, is_admin, created_at, updated_at) VALUES
            ('cli4168@hillstonenet.com', '$2b$12$1o3aPNYUkaMnzudsgWmCgedb8Fcp2Naz6xfRNhAhgZsXRFwNVIdKq', FALSE, '2024-01-01 00:00:00', '2024-01-01 00:00:00'),
            ('alice@hillstonenet.com', '$2b$12$VV0/KQk3yuoLyoVPY97gd.i6kvhe76MDeKeKE.KweL8wujRgutiz2', FALSE, '2024-01-01 00:00:00', '2024-01-01 00:00:00'),
            ('bob@hillstonenet.com', '$2b$12$nmA.ldKU.RrLNCeUojKEleNyfPaz.OIW30YOoz656W0d2YpX3xXl2', FALSE, '2024-01-01 00:00:00', '2024-01-01 00:00:00'),
            ('test@hillstonenet.com', '$2b$12$Y6e0YMXsMajQH8wba9SxV.dfwCPlOv182.Bz0MHKBjcCv7AYQAZDa', FALSE, '2024-01-01 00:00:00', '2024-01-01 00:00:00'),
            ('pytest@hillstonenet.com', '$2b$12$JqYN3d//4rQ1ycRc2skl0ONAay9xiMlP.YxtwdVeP/cTI4VUk8QZG', FALSE, '2024-01-01 00:00:00', '2024-01-01 00:00:00'),
            ('admin@hillstonenet.com', '$2b$12$Sudk7xBteJuhWmSU1MZjke.790tLOUqsnhpOmrQfgLAxlEnrkYGUa', TRUE, '2024-01-01 00:00:00', '2024-01-01 00:00:00')
            "#,
        ).await;

        if let Err(e) = insert_result {
            println!("Warning: Failed to insert seed users: {}", e);
        }

        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        manager
            .drop_table(Table::drop().table(LineageSuggestions::Table).to_owned())
            .await?;
        manager
            .drop_table(Table::drop().table(DataLineage::Table).to_owned())
            .await?;
        manager
            .drop_table(Table::drop().table(DvcMetadata::Table).to_owned())
            .await?;
        manager
            .drop_table(Table::drop().table(DataItems::Table).to_owned())
            .await?;
        manager
            .drop_table(Table::drop().table(Users::Table).to_owned())
            .await?;
        Ok(())
    }
}

#[derive(DeriveIden)]
enum Users {
    Table,
    Id,
    Email,
    HashedPassword,
    AvatarUrl,
    IsAdmin,
    CreatedAt,
    UpdatedAt,
}

#[derive(DeriveIden)]
enum DataItems {
    Table,
    Id,
    Name,
    Description,
    Project,
    Source,
    FilePath,
    FileHash,
    FileSize,
    FileType,
    IsFolder,
    FileCount,
    UserId,
    ParentId,
    IsPublic,
    CreatedAt,
    UpdatedAt,
    DvcMetadataId,
}

#[derive(DeriveIden)]
enum DvcMetadata {
    Table,
    Id,
    UserId,
    DvcFilename,
    DataFilename,
    DataFileHash,
    DataFileSize,
    DvcFilePath,
    DvcFileHash,
    Project,
    Source,
    CreatedAt,
    UpdatedAt,
}

#[derive(DeriveIden)]
enum DataLineage {
    Table,
    Id,
    ChildId,
    ParentId,
    RelationshipType,
    Confidence,
    DetectionMethod,
    Description,
    CreatedBy,
    CreatedAt,
    UpdatedAt,
}

#[derive(DeriveIden)]
enum LineageSuggestions {
    Table,
    Id,
    ChildId,
    SuggestedParentId,
    ConfidenceScore,
    SuggestionReason,
    DetectionMethod,
    Status,
    CreatedAt,
    ReviewedAt,
    ReviewedBy,
}
